﻿CREATE VIEW [dbo].[Alert]
	AS 
	SELECT	A.[Id], 
					A.[RowId], 
					A.[IsDeleted], 
					A.[CreatedDate], 
					A.[ModifiedDate], 
					A.[ModifiedByUserId], 
					A.[Version], 
					A.[CustomerCommunicationId] AS [ReferenceId], 
					CU.[ProjectId], 
					CC.[CustomerId], 
					CU.[SalesEmployeeId], 
					A.[AlertTypeId], 
					AT.[Name] + N'提醒' AS [Name], 
					REPLACE(AT.[DescriptionTemplate], N'{0}', CAST(YEAR(CC.[CommunicationDate]) AS nvarchar(4)) + N'年' + CAST(MONTH(CC.[CommunicationDate]) AS nvarchar(2)) + N'月' + CAST(DAY(CC.[CommunicationDate]) AS nvarchar(2)) + N'日') AS [Description], 
					A.[RemindDate], 
					A.[Processed], 
					A.[ProcessedDate], 
					A.[ProcessedByUserId]
	FROM		[dbo].[CustomerCommunicationScheduledCallbackAlert] AS A 
		INNER JOIN 
		(
			SELECT	[Id],
							[Name],
							[DescriptionTemplate]
			FROM		[dbo].[AlertType]
			--WHERE	[IsDeleted] = 0
		) AS AT 
		ON A.[AlertTypeId] = AT.[Id]
		INNER JOIN 
		(
			SELECT	[Id],
							[CustomerId],
							[CommunicationDate]
			FROM		[dbo].[CustomerCommunication]
			--WHERE	[IsDeleted] = 0
		) AS CC 
		ON A.[CustomerCommunicationId] = CC.[Id]
		INNER JOIN 
		(
			SELECT	[Id],
							[ProjectId],
							[SalesEmployeeId]
			FROM		[dbo].[Customer]
			--WHERE	[IsDeleted] = 0
		) AS CU 
		ON CC.[CustomerId] = CU.[Id]
	UNION
	SELECT	A.[Id], 
					A.[RowId], 
					A.[IsDeleted], 
					A.[CreatedDate], 
					A.[ModifiedDate], 
					A.[ModifiedByUserId], 
					A.[Version], 
					A.[SmallOrderId] AS [ReferenceId], 
					B.[ProjectId], 
					AG.[CustomerId], 
					AG.[SalesEmployeeId], 
					A.[AlertTypeId], 
					AT.[Name] + N'提醒' AS [Name], 
					REPLACE(AT.[DescriptionTemplate], N'{0}', CAST(YEAR(SO.[ExpiredDate]) AS nvarchar(4)) + N'年' + CAST(MONTH(SO.[ExpiredDate]) AS nvarchar(2)) + N'月' + CAST(DAY(SO.[ExpiredDate]) AS nvarchar(2)) + N'日') AS [Description], 
					A.[RemindDate], 
					A.[Processed], 
					A.[ProcessedDate], 
					A.[ProcessedByUserId]
	FROM		[dbo].[SmallOrderExpiredAlert] AS A 
		INNER JOIN 
		(
			SELECT	[Id],
							[Name],
							[DescriptionTemplate]
			FROM		[dbo].[AlertType]
			--WHERE	[IsDeleted] = 0
		) AS AT 
		ON A.[AlertTypeId] = AT.[Id]
		INNER JOIN 
		(
			SELECT	[Id],
							[AgreementId],
							[ExpiredDate]
			FROM		[dbo].[SmallOrder]
			--WHERE	[IsDeleted] = 0
		) AS SO 
		ON A.[SmallOrderId] = SO.[Id]
		INNER JOIN 
		(
			SELECT	[Id],
							[RoomId],
							[CustomerId],
							[SalesEmployeeId]
			FROM		[dbo].[Agreement]
			WHERE	[AgreementStateId] = 1
			--WHERE	[IsDeleted] = 0 AND [AgreementStateId] = 1
		) AS AG 
		ON SO.[AgreementId] = AG.[Id]
		INNER JOIN 
		(
			SELECT	[Id],
							[EntranceId]
			FROM		[dbo].[Room]
			--WHERE	[IsDeleted] = 0
		) AS R 
		ON AG.[RoomId] = R.[Id]
		INNER JOIN 
		(
			SELECT	[Id],
							[BuildingId]
			FROM		[dbo].[Entrance]
			--WHERE	[IsDeleted] = 0
		) AS E 
		ON R.[EntranceId] = E.[Id]
		INNER JOIN 
		(
			SELECT	[Id],
							[ProjectId]
			FROM		[dbo].[Building]
			--WHERE	[IsDeleted] = 0
		) AS B 
		ON E.[BuildingId] = B.[Id]
	UNION
	SELECT	A.[Id], 
					A.[RowId], 
					A.[IsDeleted], 
					A.[CreatedDate], 
					A.[ModifiedDate], 
					A.[ModifiedByUserId], 
					A.[Version], 
					A.[LargeOrderId] AS [ReferenceId], 
					B.[ProjectId], 
					AG.[CustomerId], 
					AG.[SalesEmployeeId], 
					A.[AlertTypeId], 
					AT.[Name] + N'提醒' AS [Name], 
					REPLACE(AT.[DescriptionTemplate], N'{0}', CAST(YEAR(LO.[EngagedContractDate]) AS nvarchar(4)) + N'年' + CAST(MONTH(LO.[EngagedContractDate]) AS nvarchar(2)) + N'月' + CAST(DAY(LO.[EngagedContractDate]) AS nvarchar(2)) + N'日') AS [Description], 
					A.[RemindDate], 
					A.[Processed], 
					A.[ProcessedDate], 
					A.[ProcessedByUserId]
	FROM		[dbo].[LargeOrderEngagedContractAlert] AS A 
		INNER JOIN 
		(
			SELECT	[Id],
							[Name],
							[DescriptionTemplate]
			FROM		[dbo].[AlertType]
			--WHERE	[IsDeleted] = 0
		) AS AT 
		ON A.[AlertTypeId] = AT.[Id]
		INNER JOIN 
		(
			SELECT	[Id],
							[AgreementId],
							[EngagedContractDate]
			FROM		[dbo].[LargeOrder]
			--WHERE	[IsDeleted] = 0
		) AS LO 
		ON A.[LargeOrderId] = LO.[Id]
		INNER JOIN 
		(
			SELECT	[Id],
							[RoomId],
							[CustomerId],
							[SalesEmployeeId]
			FROM		[dbo].[Agreement]
			WHERE	[AgreementStateId] = 2
			--WHERE	[IsDeleted] = 0 AND [AgreementStateId] = 2
		) AS AG 
		ON LO.[AgreementId] = AG.[Id]
		INNER JOIN 
		(
			SELECT	[Id],
							[EntranceId]
			FROM		[dbo].[Room]
			--WHERE	[IsDeleted] = 0
		) AS R 
		ON AG.[RoomId] = R.[Id]
		INNER JOIN 
		(
			SELECT	[Id],
							[BuildingId]
			FROM		[dbo].[Entrance]
			--WHERE	[IsDeleted] = 0
		) AS E 
		ON R.[EntranceId] = E.[Id]
		INNER JOIN 
		(
			SELECT	[Id],
							[ProjectId]
			FROM		[dbo].[Building]
			--WHERE	[IsDeleted] = 0
		) AS B 
		ON E.[BuildingId] = B.[Id]
	UNION
	SELECT	A.[Id], 
					A.[RowId], 
					A.[IsDeleted], 
					A.[CreatedDate], 
					A.[ModifiedDate], 
					A.[ModifiedByUserId], 
					A.[Version], 
					A.[ContractReceiptId] AS [ReferenceId], 
					B.[ProjectId], 
					AG.[CustomerId], 
					AG.[SalesEmployeeId], 
					A.[AlertTypeId], 
					AT.[Name] + N'提醒' AS [Name], 
					REPLACE(AT.[DescriptionTemplate], N'{0}', CAST(YEAR(CR.[ScheduledDate]) AS nvarchar(4)) + N'年' + CAST(MONTH(CR.[ScheduledDate]) AS nvarchar(2)) + N'月' + CAST(DAY(CR.[ScheduledDate]) AS nvarchar(2)) + N'日') AS [Description], 
					A.[RemindDate], 
					A.[Processed], 
					A.[ProcessedDate], 
					A.[ProcessedByUserId]
	FROM		[dbo].[ContractReceiptScheduledAlert] AS A 
		INNER JOIN 
		(
			SELECT	[Id],
							[Name],
							[DescriptionTemplate]
			FROM		[dbo].[AlertType]
			--WHERE	[IsDeleted] = 0
		) AS AT 
		ON A.[AlertTypeId] = AT.[Id]
		INNER JOIN 
		(
			SELECT	[Id],
							[ContractId],
							[ScheduledDate]
			FROM		[dbo].[ContractReceipt]
			--WHERE	[IsDeleted] = 0
		) AS CR 
		ON A.[ContractReceiptId] = CR.[Id]
		INNER JOIN 
		(
			SELECT	[Id],
							[AgreementId]
			FROM		[dbo].[Contract]
			--WHERE	[IsDeleted] = 0
		) AS C 
		ON CR.[ContractId] = C.[Id]
		INNER JOIN 
		(
			SELECT	[Id],
							[RoomId],
							[CustomerId],
							[SalesEmployeeId]
			FROM		[dbo].[Agreement]
			WHERE	[AgreementStateId] = 4
			--WHERE	[IsDeleted] = 0 AND [AgreementStateId] = 4
		) AS AG 
		ON C.[AgreementId] = AG.[Id]
		INNER JOIN 
		(
			SELECT	[Id],
							[EntranceId]
			FROM		[dbo].[Room]
			--WHERE	[IsDeleted] = 0
		) AS R 
		ON AG.[RoomId] = R.[Id]
		INNER JOIN 
		(
			SELECT	[Id],
							[BuildingId]
			FROM		[dbo].[Entrance]
			--WHERE	[IsDeleted] = 0
		) AS E 
		ON R.[EntranceId] = E.[Id]
		INNER JOIN 
		(
			SELECT	[Id],
							[ProjectId]
			FROM		[dbo].[Building]
			--WHERE	[IsDeleted] = 0
		) AS B 
		ON E.[BuildingId] = B.[Id]
	UNION
	SELECT	A.[Id], 
					A.[RowId], 
					A.[IsDeleted], 
					A.[CreatedDate], 
					A.[ModifiedDate], 
					A.[ModifiedByUserId], 
					A.[Version], 
					A.[LeaseOrderId] AS [ReferenceId], 
					B.[ProjectId], 
					AG.[CustomerId], 
					AG.[SalesEmployeeId], 
					A.[AlertTypeId], 
					AT.[Name] + N'提醒' AS [Name], 
					REPLACE(AT.[DescriptionTemplate], N'{0}', CAST(YEAR(LO.[EngagedContractDate]) AS nvarchar(4)) + N'年' + CAST(MONTH(LO.[EngagedContractDate]) AS nvarchar(2)) + N'月' + CAST(DAY(LO.[EngagedContractDate]) AS nvarchar(2)) + N'日') AS [Description], 
					A.[RemindDate], 
					A.[Processed], 
					A.[ProcessedDate], 
					A.[ProcessedByUserId]
	FROM		[dbo].[LeaseOrderEngagedContractAlert] AS A 
		INNER JOIN 
		(
			SELECT	[Id],
							[Name],
							[DescriptionTemplate]
			FROM		[dbo].[AlertType]
			--WHERE	[IsDeleted] = 0
		) AS AT 
		ON A.[AlertTypeId] = AT.[Id]
		INNER JOIN 
		(
			SELECT	[Id],
							[LeaseAgreementId],
							[EngagedContractDate]
			FROM		[dbo].[LeaseOrder]
			--WHERE	[IsDeleted] = 0
		) AS LO 
		ON A.[LeaseOrderId] = LO.[Id]
		INNER JOIN 
		(
			SELECT	[Id],
							[PropertyId],
							[CustomerId],
							[SalesEmployeeId]
			FROM		[dbo].[LeaseAgreement]
			WHERE	LeaseAgreementStateId = 1
			--WHERE	[IsDeleted] = 0 AND [LeaseAgreementStateId] = 1
		) AS AG 
		ON LO.[LeaseAgreementId] = AG.[Id]
		INNER JOIN 
		(
			SELECT	[Id],
							[EntranceId]
			FROM		[dbo].[Property]
			--WHERE	[IsDeleted] = 0
		) AS R 
		ON AG.[PropertyId] = R.[Id]
		INNER JOIN 
		(
			SELECT	[Id],
							[BuildingId]
			FROM		[dbo].[Entrance]
			--WHERE	[IsDeleted] = 0
		) AS E 
		ON R.[EntranceId] = E.[Id]
		INNER JOIN 
		(
			SELECT	[Id],
							[ProjectId]
			FROM		[dbo].[Building]
			--WHERE	[IsDeleted] = 0
		) AS B 
		ON E.[BuildingId] = B.[Id]
	UNION
	SELECT	A.[Id], 
					A.[RowId], 
					A.[IsDeleted], 
					A.[CreatedDate], 
					A.[ModifiedDate], 
					A.[ModifiedByUserId], 
					A.[Version], 
					A.[LeaseContractReceiptId] AS [ReferenceId], 
					B.[ProjectId], 
					AG.[CustomerId], 
					AG.[SalesEmployeeId], 
					A.[AlertTypeId], 
					AT.[Name] + N'提醒' AS [Name], 
					REPLACE(AT.[DescriptionTemplate], N'{0}', CAST(YEAR(CR.[ScheduledDate]) AS nvarchar(4)) + N'年' + CAST(MONTH(CR.[ScheduledDate]) AS nvarchar(2)) + N'月' + CAST(DAY(CR.[ScheduledDate]) AS nvarchar(2)) + N'日') AS [Description], 
					A.[RemindDate], 
					A.[Processed], 
					A.[ProcessedDate], 
					A.[ProcessedByUserId]
	FROM		[dbo].[LeaseContractReceiptScheduledAlert] AS A 
		INNER JOIN 
		(
			SELECT	[Id],
							[Name],
							[DescriptionTemplate]
			FROM		[dbo].[AlertType]
			--WHERE	[IsDeleted] = 0
		) AS AT 
		ON A.[AlertTypeId] = AT.[Id]
		INNER JOIN 
		(
			SELECT	[Id],
							[LeaseContractId],
							[ScheduledDate]
			FROM		[dbo].[LeaseContractReceipt]
			--WHERE	[IsDeleted] = 0
		) AS CR 
		ON A.[LeaseContractReceiptId] = CR.[Id]
		INNER JOIN 
		(
			SELECT	[Id],
							[LeaseAgreementId]
			FROM		[dbo].[LeaseContract]
			--WHERE	[IsDeleted] = 0
		) AS C 
		ON CR.[LeaseContractId] = C.[Id]
		INNER JOIN 
		(
			SELECT	[Id],
							[PropertyId],
							[CustomerId],
							[SalesEmployeeId]
			FROM		[dbo].[LeaseAgreement]
			WHERE	LeaseAgreementStateId = 2
			--WHERE	[IsDeleted] = 0 AND [LeaseAgreementStateId] = 2
		) AS AG 
		ON C.[LeaseAgreementId] = AG.[Id]
		INNER JOIN 
		(
			SELECT	[Id],
							[EntranceId]
			FROM		[dbo].[Property]
			--WHERE	[IsDeleted] = 0
		) AS R 
		ON AG.[PropertyId] = R.[Id]
		INNER JOIN 
		(
			SELECT	[Id],
							[BuildingId]
			FROM		[dbo].[Entrance]
			--WHERE	[IsDeleted] = 0
		) AS E 
		ON R.[EntranceId] = E.[Id]
		INNER JOIN 
		(
			SELECT	[Id],
							[ProjectId]
			FROM		[dbo].[Building]
			--WHERE	[IsDeleted] = 0
		) AS B 
		ON E.[BuildingId] = B.[Id]